﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;
using Follow.Common;

namespace Follow.DataAccess
{
    public class FuncionarioDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public FuncionarioDAO()
        {
            conn = ConnectionUtils.getConnection();
        }


        public IList<Funcionario> findAll()
        {
            IList<Funcionario> funcionario = new List<Funcionario>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from funcionario order by nome ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Funcionario func = new Funcionario();
                    func.IDFuncionario = reader.GetInt32(0);
                    func.IDUsuario = reader.GetInt32(1);
                    func.TipoFunc = reader.GetBoolean(2);
                    func.Nome = reader.GetString(3);
                    func.Cpf = reader.GetString(4);
                    func.Rg = reader.GetString(5);
                    func.DataNasc = reader.GetDateTime(6);
                    func.Endereco = reader.GetString(7);
                    func.Tel1 = reader.GetString(8);
                    func.Tel2 = reader.GetString(9);
                    func.Tel3 = reader.GetString(10);
                    func.Ativo = reader.GetBoolean(11);

                    funcionario.Add(func);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }

            return funcionario;
        }

        public IList<Funcionario> findAtivos()
        {
            IList<Funcionario> funcionario = new List<Funcionario>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from funcionario order by nome where ativo = 1 ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Funcionario func = new Funcionario();
                    func.IDFuncionario = reader.GetInt32(0);
                    func.IDUsuario = reader.GetInt32(1);
                    func.TipoFunc = reader.GetBoolean(2);
                    func.Nome = reader.GetString(3);
                    func.Cpf = reader.GetString(4);
                    func.Rg = reader.GetString(5);
                    func.DataNasc = reader.GetDateTime(6);
                    func.Endereco = reader.GetString(7);
                    func.Tel1 = reader.GetString(8);
                    func.Tel2 = reader.GetString(9);
                    func.Tel3 = reader.GetString(10);
                    func.Ativo = reader.GetBoolean(11);

                    funcionario.Add(func);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return funcionario;
        }

        public void insert(Funcionario f, int idUsuario)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " insert into funcionario (usuario_idusuario, tipofunc, nome, cpf, rg, " +
                                        " datanascimento, endereco, tel1, tel2, tel3, ativo) values " +
                                        " (@usuario_idusuario, @tipofunc, @nome, @cpf, @rg, " +
                                        " @datanascimento, @endereco, @tel1, @tel2, @tel3, @ativo) ";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("usuario_idusuario", f.IDUsuario));
                    cmd.Parameters.Add(new SqlParameter("tipofunc", f.TipoFunc));
                    cmd.Parameters.Add(new SqlParameter("nome", f.Nome));
                    cmd.Parameters.Add(new SqlParameter("cpf", f.Cpf));
                    cmd.Parameters.Add(new SqlParameter("rg", f.Rg));
                    cmd.Parameters.Add(new SqlParameter("datanascimento", f.DataNasc));
                    cmd.Parameters.Add(new SqlParameter("endereco", f.Endereco));
                    cmd.Parameters.Add(new SqlParameter("tel1", f.Tel1));
                    cmd.Parameters.Add(new SqlParameter("tel2", f.Tel2));
                    cmd.Parameters.Add(new SqlParameter("tel3", f.Tel3));
                    cmd.Parameters.Add(new SqlParameter("ativo", f.Ativo));
                    
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
         LogDAO log = new LogDAO();
         log.insert(idUsuario, SQLStringSingleton.GetCommandText(cmd), "Insert", "Funcionário");
        }

        public void update(Funcionario f, int idUsuario)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = @" update funcionario set usuario_idusuario = @idusuario, tipofunc = @tipofunc, 
                                        nome = @nome, cpf = @cpf, rg = @rg, datanascimento = @datanasc, endereco =
                                        @endereco, tel1 = @tel1, tel2 = @tel2, tel3 = @tel3, ativo = @ativo
                                        where idfuncionario = @idfuncionario ";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("idfuncionario", f.IDFuncionario));
                    cmd.Parameters.Add(new SqlParameter("idusuario", f.IDUsuario));
                    cmd.Parameters.Add(new SqlParameter("tipofunc", f.TipoFunc));
                    cmd.Parameters.Add(new SqlParameter("nome", f.Nome));
                    cmd.Parameters.Add(new SqlParameter("cpf", f.Cpf));
                    cmd.Parameters.Add(new SqlParameter("rg", f.Rg));
                    cmd.Parameters.Add(new SqlParameter("datanasc", f.DataNasc));
                    cmd.Parameters.Add(new SqlParameter("endereco", f.Endereco));
                    cmd.Parameters.Add(new SqlParameter("tel1", f.Tel1));
                    cmd.Parameters.Add(new SqlParameter("tel2", f.Tel2));
                    cmd.Parameters.Add(new SqlParameter("tel3", f.Tel3));
                    cmd.Parameters.Add(new SqlParameter("ativo", f.Ativo));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, SQLStringSingleton.GetCommandText(cmd), "Update", "Funcionário");
        }

        public void delete(int id, int idUsuario)
        {
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " delete from funcionario where idfuncionario = @id ";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("id", id));
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, SQLStringSingleton.GetCommandText(cmd), "Delete", "Funcionario");
        }

        public Funcionario findById(int id)
        {
            Funcionario f = null;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " select * from funcionario where idfuncionario = @id ";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("id", id));
                    reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        f = new Funcionario();
                        f.IDFuncionario = reader.GetInt32(0);
                        f.IDUsuario = reader.GetInt32(1);
                        f.TipoFunc = reader.GetBoolean(2);
                        f.Nome = reader.GetString(3);
                        f.Cpf = reader.GetString(4);
                        f.Rg = reader.GetString(5);
                        f.DataNasc = reader.GetDateTime(6);
                        f.Endereco = reader.GetString(7);
                        f.Tel1 = reader.GetString(8);
                        f.Tel2 = reader.GetString(9);
                        f.Tel3 = reader.GetString(10);
                        f.Ativo = reader.GetBoolean(11);
                    }
                }

                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }

                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            return f;
        }

        public IList<Funcionario> findByName(String nome, Boolean ativo)
        {
            IList<Funcionario> listafunc = new List<Funcionario>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from funcionario where nome like '%" + nome + "%' " +
                                    " and ativo = @ativo order by nome ";               
                cmd = new SqlCommand(sqlCommand, conn);
                cmd.Parameters.Add(new SqlParameter("ativo", ativo));
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Funcionario f = new Funcionario();
                    f.IDFuncionario     = reader.GetInt32(0);
                    f.IDUsuario         = reader.GetInt32(1);
                    f.TipoFunc          = reader.GetBoolean(2);
                    f.Nome              = reader.GetString(3);
                    f.Cpf               = reader.GetString(4);
                    f.Rg                = reader.GetString(5);
                    f.DataNasc          = reader.GetDateTime(6);
                    f.Endereco          = reader.GetString(7);
                    f.Tel1              = reader.GetString(8);
                    f.Tel2              = reader.GetString(9);
                    f.Tel3              = reader.GetString(10);
                    f.Ativo             = reader.GetBoolean(11);
                    listafunc.Add(f);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return listafunc;
        }

        public IList<Funcionario> findByNaoGestores()
        {
            IList<Funcionario> listafunc = new List<Funcionario>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from funcionario where tipofunc = 0" +
                                    " and ativo = 1 order by nome;";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Funcionario f = new Funcionario();
                    f.IDFuncionario = reader.GetInt32(0);
                    f.IDUsuario = reader.GetInt32(1);
                    f.TipoFunc = reader.GetBoolean(2);
                    f.Nome = reader.GetString(3);
                    f.Cpf = reader.GetString(4);
                    f.Rg = reader.GetString(5);
                    f.DataNasc = reader.GetDateTime(6);
                    f.Endereco = reader.GetString(7);
                    f.Tel1 = reader.GetString(8);
                    f.Tel2 = reader.GetString(9);
                    f.Tel3 = reader.GetString(10);
                    f.Ativo = reader.GetBoolean(11);
                    listafunc.Add(f);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return listafunc;
        }

        public IList<Funcionario> findByGestores()
        {
            IList<Funcionario> listafunc = new List<Funcionario>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from funcionario where tipofunc = 'true'" +
                                    "order by nome ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Funcionario f = new Funcionario();
                    f.IDFuncionario = reader.GetInt32(0);
                    f.IDUsuario = reader.GetInt32(1);
                    f.TipoFunc = reader.GetBoolean(2);
                    f.Nome = reader.GetString(3);
                    f.Cpf = reader.GetString(4);
                    f.Rg = reader.GetString(5);
                    f.DataNasc = reader.GetDateTime(6);
                    f.Endereco = reader.GetString(7);
                    f.Tel1 = reader.GetString(8);
                    f.Tel2 = reader.GetString(9);
                    f.Tel3 = reader.GetString(10);
                    f.Ativo = reader.GetBoolean(11);
                    listafunc.Add(f);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return listafunc;
        }

        public IList<Funcionario> findByName(String nome)
        {
            IList<Funcionario> listafunc = new List<Funcionario>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from funcionario where nome like '%" + nome + "%' " +
                                    " order by nome ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Funcionario f = new Funcionario();
                    f.IDFuncionario = reader.GetInt32(0);
                    f.IDUsuario = reader.GetInt32(1);
                    f.TipoFunc = reader.GetBoolean(2);
                    f.Nome = reader.GetString(3);
                    f.Cpf = reader.GetString(4);
                    f.Rg = reader.GetString(5);
                    f.DataNasc = reader.GetDateTime(6);
                    f.Endereco = reader.GetString(7);
                    f.Tel1 = reader.GetString(8);
                    f.Tel2 = reader.GetString(9);
                    f.Tel3 = reader.GetString(10);
                    f.Ativo = reader.GetBoolean(11);
                    listafunc.Add(f);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return listafunc;
        }

        public Funcionario findByIdUsuario(Int32 id)
        {
            Funcionario f = null;
            try
            {
                conn.Open();
                String sqlCommand = " select * from funcionario where usuario_idusuario = " + id;
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                if (reader.Read())
                {
                    f = new Funcionario();
                    f.IDFuncionario = reader.GetInt32(0);
                    f.IDUsuario = reader.GetInt32(1);
                    f.TipoFunc = reader.GetBoolean(2);
                    f.Nome = reader.GetString(3);
                    f.Cpf = reader.GetString(4);
                    f.Rg = reader.GetString(5);
                    f.DataNasc = reader.GetDateTime(6);
                    f.Endereco = reader.GetString(7);
                    f.Tel1 = reader.GetString(8);
                    f.Tel2 = reader.GetString(9);
                    f.Tel3 = reader.GetString(10);
                    f.Ativo = reader.GetBoolean(11);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return f;
        }
    }
}